from flask import Flask
from flask import render_template,url_for
from flask import request 
from openpyxl import Workbook
from openpyxl import load_workbook
import sqlite3
import webbrowser
from flask_db import *

#pip install flask
# db=sqlite3.connect(str(root/"yj.db"), check_same_thread=False)
# cur = db.cursor()
#print (file)

@app.errorhandler(500)
def internal_server_error(e):
    return "error"
@app.route('/')
def index(name=None):
    return render_template('index.html', name=name)
@app.route('/test' ,methods=['POST', 'GET'])
def test():
    return "getkc"+request.args.get('key', '')

@app.route("/getkch")
def getkch():
    global root
    file=root/pathlib.Path("班级座位.xlsx")
    wb = load_workbook(str(file))
    wb_active=wb.active
    wb_row=wb_active.rows
    room={}
    for i in range(2,wb_active.max_column+1):   
        #print(wb_active.cell(row=1,column=i).value)
        name=str(str(wb_active.cell(row=1,column=i).value))
        array=[]
        for n in range(2,wb_active.max_row+1):
            if(wb_active.cell(row=n,column=i).value!=None):
                array.append(wb_active.cell(row=n,column=i).value)

        room[name]=(getExamAlias(name),len(array),array)
    wb.close()
    return room

@app.route("/getStudentInfo",methods=['GET'])
def getStudentInfo():
    kc=request.args.get("kc")
    #sql="select * from main where kc ='%s'"%(str(kc))
    sql="select * from main where kc = \'{kc}\'".format(kc=kc)
    # r=cur.execute(sql)
    data={}
    for i in  query_db(sql):
        data[str(i[0])] = i
    print(data)

    return data

@app.route("/getEmptyStudent")
def getEmptyStudent():
    sql="select * from main where kc = \'0\' or kc is null"
    # r=cur.execute(sql)
    data={}
    for i in  query_db(sql):
        data[str(i[0])] = i
    print(data)
    return data

@app.route('/setsutdent' ,methods=['POST'])
def setsutdent():
    db=get_db()
    cur = db.cursor()
    id=request.form["id"]
    kc=request.form["kc"]
    z=request.form["z"]
    p=request.form["p"]
    if(id=="delete"):
        cur.execute("UPDATE main SET 'kc' = '0', 'z' = '0', 'p' = '0' WHERE kc='{kc}' and z='{z}' and p='{p}'".format(kc=kc,z=z,p=p))
        db.commit()
    else:
        sql="select id from main where kc='{kc}' and z='{z}' and p='{p}'".format(kc=kc,z=z,p=p)
        r=cur.execute(sql)
        data=r.fetchall()
        if(len(data)>0):
            for i in data:
                print(i[0])
                cur.execute("UPDATE main SET 'kc' = '0', 'z' = '0', 'p' = '0' WHERE ID = {id}".format(id=i[0]))
            db.commit()
        cur.execute("UPDATE main SET 'kc' = '{kc}', 'z' = '{z}', 'p' = '{p}' WHERE ID = {id}".format(kc=kc,z=z,p=p,id=id))
        db.commit()
    return id

'''
webview.create_window('Flask',app,width=1800,height=1000)
webview.start(gui="cef")

'''
port=18080
if __name__== '__main__':
    # webbrowser.open("http://[::1]:{0}".format(port))    
    app.jinja_env.auto_reload = True
    app.config['TEMPLATES_AUTO_RELOAD'] = True
    app.run(
        debug=True
        # ,host = '::'
        # ,port = port
    )
    app.DEBUG=True

   
